package com.dsg.util;

import com.monitorjbl.xlsx.StreamingReader;
import com.powerbridge.core.util.Reflections;
import com.powerbridge.core.util.toolbox.StringUtil;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Time;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 以迭代器的方式进行excel数据的导出。
 * Created by xinrongli on 2019/5/22.
 */
@Slf4j
public class ExcelIteratorReader<T> implements Iterable {
    private ExcelReadIterator iterator;
    private Iterator rowIterator;
    private int rowBegin;
    private Class entity;
    private Map<Integer, String> indexNameMap;
    private List formatErrorList;
    private List<String> fieldNameList = new ArrayList<>();
    private List<String> fieldCnNameList = new ArrayList<>();
    private List<String> fieldTypeList;
    private int rowCount = 0;
    //第一行的总列数
    private int totalCells = 0;
    private File file;
    private int sheetAt;


    /**
     * @param file            要导入的文件
     * @param entity          实体类型，entityObject中的字段通过@ApiModelProperty与excel中的列对应
     * @param sheetAt         excel sheet index,>=0
     * @param rowBegin        数据开始行，>=0
     * @param formatErrorList 用于保存每条导入失败的数据的信息
     * @throws Exception
     */
    public ExcelIteratorReader(File file, Class entity, int sheetAt, int rowBegin, List formatErrorList) throws Exception {
        this.entity = entity;
        this.rowBegin = rowBegin;
        this.formatErrorList = formatErrorList;
        iterator = new ExcelReadIterator();
        this.file = file;
        this.sheetAt = sheetAt;
        try {
            Workbook workBook = StreamingReader.builder().open(file);
            Sheet sheet = workBook.getSheetAt(sheetAt);
            rowIterator = sheet.rowIterator();
            indexNameMap = getIndexNameMap();

            fieldTypeList = new ArrayList<>();
            for (String fieldName :
                    fieldNameList) {
                String fieldType = this.entity.getDeclaredField(fieldName).getType().getSimpleName();
                fieldTypeList.add(fieldType);
            }
        } catch (OLE2NotOfficeXmlFileException e) {
            e.printStackTrace();
            throw new OLE2NotOfficeXmlFileException("只支持XLSX文件的導入！");
        } catch (NumberFormatException e) {
            e.printStackTrace();
            throw new NumberFormatException();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    /**
     * 重置迭代器
     */
    public void resetIterator() {
        Workbook workBook = StreamingReader.builder().open(file);
        Sheet sheet = workBook.getSheetAt(sheetAt);
        rowIterator = sheet.rowIterator();
        //第一行是列名，直接跳过
        rowIterator.next();
    }

    private Map getIndexNameMap() {
        Map<Integer, String> indexNameMap = new HashMap<>();
        int rowIndex = 0;
        Row firstRow = null;
        while (rowIterator.hasNext()) {
            if (rowIndex++ == rowBegin) {
                firstRow = ((Row) rowIterator.next());
                totalCells = firstRow.getPhysicalNumberOfCells();
                break;
            } else {
                rowIterator.next();
            }
        }
        //取得列名的 中文->英文 键值对
        Map<String, String> cnEnTitleMap = getCnEnTitleMap(entity);
        if (firstRow != null) {
            // 第一行为列名，通过其取得excel中能与entityObject中的各字段对应上的各列的 index->英文列名 的键值对
            for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                Cell cell = firstRow.getCell(cellIndex);
                if (cell == null) {
                    totalCells = cellIndex;
                    break;
                }
                String cellValue = cell.getStringCellValue().trim();
                cellValue = cellValue.replace("（", "(").replace("）", ")");
                if (cnEnTitleMap.containsKey(cellValue)) {
                    Object name = cnEnTitleMap.get(cellValue);
                    if (name != null && StringUtil.isNotEmpty((String) name)) {
                        indexNameMap.put(cellIndex, (String) name);
                        fieldCnNameList.add(cellValue);
                        fieldNameList.add((String) name);
                    }
                }
            }
        }
        if (indexNameMap.size() == 0) {
            throw new NullPointerException("rowBegin有誤！");
        }
        return indexNameMap;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                // 数字
                case NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);
                        cellValue = sdf.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("#.#########");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    return cellValue;
                // 字符串
                case STRING:
                    cellValue = cell.getStringCellValue().trim();
                    return cellValue;
                // Boolean
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";
                    return cellValue;
                // 公式
                case FORMULA:
                    cellValue = cell.getCellFormula() + "";
                    return cellValue;
                // 空值
                case BLANK:
                    cellValue = "";
                    return cellValue;
                // 故障
                case ERROR:
                    cellValue = "非法字符";
                    return cellValue;
                default:
                    cellValue = "未知類型";
            }
        }
        return cellValue;
    }

    private static Map getCnEnTitleMap(Class clazz) {
        Map<String, String> columnMap = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field :
                fields) {
            ApiModelProperty apiModelProperty = field.getAnnotation(ApiModelProperty.class);
            if (apiModelProperty != null) {
                String keys = apiModelProperty.value();
                keys = keys.replace("（", "(").replace("）", ")");
                String value = field.getName();
                if (keys.contains(",")) {
                    for (String key : keys.split(",")) {
                        columnMap.put(key, value);
                    }
                } else {
                    columnMap.put(keys, value);
                }
            }
        }
        return columnMap;
    }

    private static Object getProperValue(String value, String fieldType) throws Exception {
        Object properValue = StringUtil.notBlank(value) ? value : null;
        if (null != value && !"".equals(value)) {
            try {
                switch (fieldType) {
                    case "Date":
                        properValue = parseDate(value);
                        break;
                    case "Integer":
                    case "int":
                        properValue = Integer.parseInt(value);
                        break;
                    case "Long":
                        properValue = Long.parseLong(value);
                        break;
                    case "Double":
                        properValue = Double.parseDouble(value);
                        break;
                    case "BigDecimal":
                        properValue = new BigDecimal(value);
                        break;
                    case "Boolean":
                        properValue = Boolean.parseBoolean(value);
                        break;
                    case "Time":
                        properValue = parseTime(value);
                        break;
                    default:
                        properValue = value;
                        break;
                }
            } catch (NumberFormatException e) {
                throw e;
            } catch (Exception e) {
                throw e;
            }
        }
        return properValue;
    }

    /**
     * 格式化string为Date
     *
     * @param dateStr
     * @return date
     */
    private static Date parseDate(String dateStr) throws Exception {
        if (null == dateStr || "".equals(dateStr)) {
            return null;
        }
        String fmtStr;
        try {
            if (dateStr.indexOf("-") > 0) {
                if (dateStr.indexOf(":") > 0) {
                    fmtStr = "yyyy-MM-dd HH:mm:ss";
                } else {
                    fmtStr = "yyyy-MM-dd";
                }
            } else {
                if (dateStr.indexOf("/") > 0) {
                    fmtStr = "dd/MM/yyyy";
                } else {
                    if (dateStr.length() == 8) {
                        fmtStr = "yyyyMMdd";
                    } else {
                        fmtStr = "yyyy-MM-dd HH:mm:ss";
                    }
                }
            }

            SimpleDateFormat sdf = new SimpleDateFormat(fmtStr, Locale.UK);
            return sdf.parse(dateStr);
        } catch (ParseException e) {
            throw new ParseException("YYYY-MM-DD,如2018-09-01", e.getErrorOffset());
        } catch (Exception e) {
            throw e;
        }
    }

    private static Time parseTime(String timeStr) throws Exception {
        if (null == timeStr || "".equals(timeStr)) {
            return null;
        }
        String fmtStr;
        try {
            if (timeStr.indexOf(':') > 0) {
                fmtStr = "HH:mm:ss";
            } else {
                fmtStr = "HHmmss";
            }
            SimpleDateFormat hmsFormat = new SimpleDateFormat(fmtStr);
            String nowTimes = hmsFormat.format(new Date(hmsFormat.parse(timeStr).getTime()));
            Time time = new Time(hmsFormat.parse(nowTimes).getTime());
            return time;
        } catch (ParseException e) {
            throw new ParseException("HH:mm:ss,如210215", e.getErrorOffset());
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public Iterator iterator() {
        return iterator;
    }

    class ExcelReadIterator implements Iterator {
        @Override
        public boolean hasNext() {
            return rowIterator.hasNext();
        }

        @Override
        public Object next() {
            Cell cell;

            if (rowIterator.hasNext()) {
                Row row = (Row) rowIterator.next();
                if (row == null) {
                    return null;
                }

                List cellValueList = new ArrayList();
                boolean isEmptyRow = true;
                for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                    String cellValue = "";
                    if (indexNameMap.containsKey(cellIndex) && !"".equals(indexNameMap.get(cellIndex))) {
                        cell = row.getCell(cellIndex);
                        cellValue = getCellValue(cell);
                        if (isEmptyRow && StringUtil.isNotEmpty(cellValue)) {
                            isEmptyRow = false;
                        }
                        cellValueList.add(cellValue);
                    }
                }

                if (isEmptyRow) {
                    return null;
                }

                rowCount++;
                try {
                    Object entityObject = convertToEntity(cellValueList, entity, formatErrorList);
                    return (T) entityObject;
                } catch (Exception e) {
                    log.error("轉成實體時發生異常!");
                    e.printStackTrace();
                    return null;
                }
            }

            return null;
        }

        private Object convertToEntity(List<String> valueList, Class clazz, List formatErrorList) throws Exception {
            Object newEntity = clazz.newInstance();
            String fieldName;
            String fieldCnName;
            String fieldType;
            for (int j = 0; j < fieldNameList.size(); j++) {
                fieldName = fieldNameList.get(j);
                fieldType = fieldTypeList.get(j);
                fieldCnName = fieldCnNameList.get(j);
                try {
                    Object properValue = getProperValue(valueList.get(j), fieldType);
                    Reflections.setFieldValue(newEntity, fieldName, properValue);
                } catch (NumberFormatException e) {
                    if (formatErrorList != null) {
                        formatErrorList.add(String.format("第%s行數據中的 %s 數據類型有問題！其值為：%s", rowCount + rowBegin + 1, fieldCnName, valueList.get(j)));
                    }
                    e.printStackTrace();
                } catch (ParseException e) {
                    if (formatErrorList != null) {
                        formatErrorList.add(String.format("第%s行數據中的 %s 日期格式有問題！其值為：%s, 正确格式應為：%s", rowCount + rowBegin + 1, fieldCnName, valueList.get(j), e.getMessage()));
                    }
                    e.printStackTrace();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return newEntity;
        }
    }

    /**
     * MultipartFile 转换成File
     *
     * @param multfile 原文件类型
     * @return File
     * @throws IOException
     */
    public static File multipartToFile(MultipartFile multfile) throws IOException {
        CommonsMultipartFile cf = (CommonsMultipartFile) multfile;
        //这个myfile是MultipartFile的
        DiskFileItem fi = (DiskFileItem) cf.getFileItem();
        File file = fi.getStoreLocation();
        //手动创建临时文件multipartResolver
        if (file.length() < 2048) {
            File tmpFile = new File(System.getProperty("java.io.tmpdir") + System.getProperty("file.separator") +
                    file.getName());
            multfile.transferTo(tmpFile);
            return tmpFile;
        }
        return file;
    }
}

